{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Virtual Manipulation of Datasets Tutorial\n",
    "\n",
    "In this tutorial, we will be using two [Dataset types](../../../../doc/#builtin/datasets/Datasets.md.html), namely [Sampled Dataset](../../../../doc/#builtin/datasets/SampledDataset.md.html) and [Transposed Dataset](../../../../doc/#builtin/datasets/TransposedDataset.md.html). This tutorial assumes familiarity with [Procedures](../../../../doc/#builtin/procedures/Procedures.md.html). We suggest going through the [Procedures and Functions Tutorial](../../../../ipy/notebooks/_tutorials/_latest/Procedures%20and%20Functions%20Tutorial.ipynb) beforehand.\n",
    "\n",
    "To run the examples below, we created a toy dataset using the description of machine learning concepts from [Wikipedia](https://en.wikipedia.org). Our dataset is made up of two columns. The first column contains the name of the machine learning concept. The second column contains the corresponding description."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The notebook cells below use `pymldb`'s `Connection` class to make [REST API](../../../../doc/#builtin/WorkingWithRest.md.html) calls. You can check out the [Using `pymldb` Tutorial](../../../../doc/nblink.html#_tutorials/Using pymldb Tutorial) for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pymldb import Connection\n",
    "mldb = Connection(\"http://localhost\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the Dataset\n",
    "A powerful feature of MLDB allows us to execute SQL code while loading data. Here, the [`tokenize`](../../../../doc/#builtin/sql/ValueExpression.md.html#builtinfunctions) function first splits the columns into (lowercased) individual words, removing words with a length of less than 4 characters. Notice that we have chosen to use a dataset of type [sparse.mutable](../../../../doc/#builtin/datasets/MutableSparseMatrixDataset.md.html) since we do know ahead time the column size for each row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/import_ML_concepts', {\n",
    "        \"type\":\"import.text\",\n",
    "        \"params\": \n",
    "        {\n",
    "            \"dataFileUrl\":\"http://public.mldb.ai/datasets/MachineLearningConcepts.csv\",\n",
    "            \"outputDataset\":{\n",
    "                \"id\":\"ml_concepts\", # can be accessed later using id\n",
    "                \"type\": \"sparse.mutable\" # sparse mutable dataset is needed since we tokenize words below\n",
    "            },\n",
    "            \"named\": \"Concepts\", #row name expression for output dataset\n",
    "            \"select\": \n",
    "                \"\"\" \n",
    "                    tokenize(\n",
    "                        lower(Text), \n",
    "                        {splitChars: ' -''\"?!;:/[]*,().',  \n",
    "                        minTokenLength: 4}) AS *\n",
    "                \"\"\", # within the tokenize function:\n",
    "                        # lower case\n",
    "                        # leave out punctuation and spaces\n",
    "                        # allow only words > 4 characters\n",
    "            \"runOnCreation\": True\n",
    "        }\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A quick look at the data\n",
    "\n",
    "We can use the [Query API](../../../../doc/#builtin/sql/QueryAPI.md.html) to get the data into a Pandas DataFrame to take a quick look at it. You will notice that certain cells have a 'NaN' value as seen below. This is because the dataset is sparse: every word is not present in the description of every concept. Those missing values are representend as NaNs in a Pandas DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>addition</th>\n",
       "      <th>algorithm</th>\n",
       "      <th>algorithms</th>\n",
       "      <th>also</th>\n",
       "      <th>analysis</th>\n",
       "      <th>analyze</th>\n",
       "      <th>applications</th>\n",
       "      <th>approach</th>\n",
       "      <th>assigns</th>\n",
       "      <th>associated</th>\n",
       "      <th>...</th>\n",
       "      <th>popularized</th>\n",
       "      <th>provide</th>\n",
       "      <th>rather</th>\n",
       "      <th>recurrent</th>\n",
       "      <th>serve</th>\n",
       "      <th>stored</th>\n",
       "      <th>systems</th>\n",
       "      <th>threshold</th>\n",
       "      <th>understanding</th>\n",
       "      <th>wrong</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Support vector machine</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logistic regression</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep belief network</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hopfield network</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 286 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               addition  algorithm  algorithms  also  \\\n",
       "_rowName                                                               \n",
       "Support vector machine                1          2           1     1   \n",
       "Logistic regression                 NaN        NaN         NaN   NaN   \n",
       "Deep belief network                 NaN        NaN         NaN     1   \n",
       "Restricted boltzmann machines       NaN          1           2   NaN   \n",
       "Hopfield network                    NaN        NaN         NaN     1   \n",
       "\n",
       "                               analysis  analyze  applications  approach  \\\n",
       "_rowName                                                                   \n",
       "Support vector machine                1        1             1         1   \n",
       "Logistic regression                 NaN      NaN           NaN       NaN   \n",
       "Deep belief network                 NaN      NaN           NaN       NaN   \n",
       "Restricted boltzmann machines       NaN      NaN             1       NaN   \n",
       "Hopfield network                    NaN      NaN           NaN       NaN   \n",
       "\n",
       "                               assigns  associated  ...    popularized  \\\n",
       "_rowName                                            ...                  \n",
       "Support vector machine               1           1  ...            NaN   \n",
       "Logistic regression                NaN         NaN  ...            NaN   \n",
       "Deep belief network                NaN         NaN  ...            NaN   \n",
       "Restricted boltzmann machines      NaN         NaN  ...            NaN   \n",
       "Hopfield network                   NaN         NaN  ...              1   \n",
       "\n",
       "                               provide  rather  recurrent  serve  stored  \\\n",
       "_rowName                                                                   \n",
       "Support vector machine             NaN     NaN        NaN    NaN     NaN   \n",
       "Logistic regression                NaN     NaN        NaN    NaN     NaN   \n",
       "Deep belief network                NaN     NaN        NaN    NaN     NaN   \n",
       "Restricted boltzmann machines      NaN     NaN        NaN    NaN     NaN   \n",
       "Hopfield network                     1       1          1      1       1   \n",
       "\n",
       "                               systems  threshold  understanding  wrong  \n",
       "_rowName                                                                 \n",
       "Support vector machine             NaN        NaN            NaN    NaN  \n",
       "Logistic regression                NaN        NaN            NaN    NaN  \n",
       "Deep belief network                NaN        NaN            NaN    NaN  \n",
       "Restricted boltzmann machines      NaN        NaN            NaN    NaN  \n",
       "Hopfield network                     1          1              1      1  \n",
       "\n",
       "[5 rows x 286 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"SELECT * FROM ml_concepts LIMIT 5\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring the data\n",
    "Let's count the number of words that describes each concept."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Support vector machine</th>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logistic regression</th>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Deep belief network</th>\n",
       "      <td>98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hopfield network</th>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naive bayes classifier</th>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Boltzmann machine</th>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Autoencoder</th>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Artificial neural network</th>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               count\n",
       "_rowName                            \n",
       "Support vector machine           149\n",
       "Logistic regression               99\n",
       "Deep belief network               98\n",
       "Restricted boltzmann machines    124\n",
       "Hopfield network                  53\n",
       "Naive bayes classifier           165\n",
       "Boltzmann machine                 89\n",
       "Autoencoder                       31\n",
       "Artificial neural network         40"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\n",
    "    \"\"\"\n",
    "    SELECT horizontal_sum({*}) AS count\n",
    "    FROM ml_concepts\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Taking the transpose of data\n",
    "There are two ways to take the transpose of a dataset:\n",
    "    1. using a transposed dataset\n",
    "    2. using a transpose SQL function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put(\"/v1/datasets/transposed_concepts\", {\n",
    "        \"type\": \"transposed\",\n",
    "        \"params\": \n",
    "        {\n",
    "            \"dataset\": {\"id\":\"ml_concepts\"}\n",
    "        }\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code above transposed the data and created the <code>transposed_concepts</code> dataset. Now that we have transposed our data, we can easily see which words are most frequently used in the Machine Learning concept descriptions. Please note that we have joined below the results from the <code>transposed_concepts</code> dataset and the transpose inline function to compare outputs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>top_words_transp_dataset</th>\n",
       "      <th>top_words_transp_function</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>learning</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>with</th>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>neural</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>machine</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>training</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>used</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>machines</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>network</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>model</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>which</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          top_words_transp_dataset  top_words_transp_function\n",
       "_rowName                                                     \n",
       "learning                         7                          7\n",
       "with                             7                          7\n",
       "neural                           6                          6\n",
       "machine                          6                          6\n",
       "training                         5                          5\n",
       "used                             5                          5\n",
       "machines                         5                          5\n",
       "network                          5                          5\n",
       "model                            5                          5\n",
       "which                            4                          4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\n",
    "    \"\"\"\n",
    "        SELECT \n",
    "            horizontal_count({a.*}) AS top_words_transp_dataset,\n",
    "            horizontal_count({b.*}) AS top_words_transp_function\n",
    "        NAMED a.rowName()\n",
    "        FROM transposed_concepts AS a\n",
    "        JOIN transpose(ml_concepts) AS b\n",
    "            ON a.rowName() = b.rowName()\n",
    "            ORDER BY top_words_transp_dataset DESC\n",
    "            LIMIT 10\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While the methods yield similar results, the transposed dataset allows the user to keep the output dataset in memory. This may be useful if you want to use the dataset in multiple steps of your data analysis. In cases where you don't need to use the transpose more than once, you can simply use the inline syntax."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Taking a sample of data\n",
    "There are two ways to take samples of dataset:\n",
    "    1. using a sampled dataset \n",
    "    2. using a sample SQL function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put(\"/v1/datasets/sampled_tokens\", {\n",
    "        \"type\": \"sampled\",\n",
    "        \"params\": \n",
    "        {\n",
    "            \"rows\": 10,\n",
    "            \"withReplacement\": False,\n",
    "            \"dataset\": {\"id\":\"transposed_concepts\"},\n",
    "            \"seed\": 0\n",
    "        }\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Naive bayes classifier</th>\n",
       "      <th>Artificial neural network</th>\n",
       "      <th>Boltzmann machine</th>\n",
       "      <th>Restricted boltzmann machines</th>\n",
       "      <th>Support vector machine</th>\n",
       "      <th>Deep belief network</th>\n",
       "      <th>Logistic regression</th>\n",
       "      <th>Hopfield network</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>maximum</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nervous</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>random</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>studied</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>neurons</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symmetric</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>marked</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>such</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hopfield</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>only</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Naive bayes classifier  Artificial neural network  \\\n",
       "_rowName                                                       \n",
       "maximum                         1                        NaN   \n",
       "nervous                       NaN                          1   \n",
       "random                        NaN                        NaN   \n",
       "studied                         1                        NaN   \n",
       "neurons                       NaN                        NaN   \n",
       "symmetric                     NaN                        NaN   \n",
       "marked                        NaN                        NaN   \n",
       "such                            1                        NaN   \n",
       "hopfield                      NaN                        NaN   \n",
       "only                          NaN                        NaN   \n",
       "\n",
       "           Boltzmann machine  Restricted boltzmann machines  \\\n",
       "_rowName                                                      \n",
       "maximum                  NaN                            NaN   \n",
       "nervous                  NaN                            NaN   \n",
       "random                     1                            NaN   \n",
       "studied                  NaN                            NaN   \n",
       "neurons                  NaN                              1   \n",
       "symmetric                NaN                              1   \n",
       "marked                   NaN                            NaN   \n",
       "such                     NaN                            NaN   \n",
       "hopfield                   1                            NaN   \n",
       "only                     NaN                            NaN   \n",
       "\n",
       "           Support vector machine  Deep belief network  Logistic regression  \\\n",
       "_rowName                                                                      \n",
       "maximum                       NaN                  NaN                  NaN   \n",
       "nervous                       NaN                  NaN                  NaN   \n",
       "random                        NaN                  NaN                  NaN   \n",
       "studied                       NaN                  NaN                  NaN   \n",
       "neurons                       NaN                  NaN                  NaN   \n",
       "symmetric                     NaN                  NaN                  NaN   \n",
       "marked                          1                  NaN                  NaN   \n",
       "such                          NaN                    1                    1   \n",
       "hopfield                      NaN                  NaN                  NaN   \n",
       "only                            1                  NaN                  NaN   \n",
       "\n",
       "           Hopfield network  \n",
       "_rowName                     \n",
       "maximum                 NaN  \n",
       "nervous                 NaN  \n",
       "random                  NaN  \n",
       "studied                 NaN  \n",
       "neurons                 NaN  \n",
       "symmetric               NaN  \n",
       "marked                  NaN  \n",
       "such                    NaN  \n",
       "hopfield                  4  \n",
       "only                    NaN  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"SELECT * FROM sampled_tokens\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "source": [
    "The above code would be simlar to the following SQL function:\n",
    "```python\n",
    "mldb.query(\n",
    "    \"\"\"\n",
    "    SELECT * \n",
    "    FROM sample(\n",
    "        transposed_concepts, \n",
    "        {\n",
    "            rows: 10,\n",
    "            withReplacement: False\n",
    "        }\n",
    "    )                      \n",
    "    \"\"\"\n",
    ") \n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, the two methods provide the same desired outcome, allowing you to choose how to best manipulate your data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Using datasets vs. SQL functions\n",
    "As seen above, the two different ways to either transpose or sample data are equivalent. It is recommended to use Dataset types instead of SQL functions when the created table will be reused or called later in the program.\n",
    "\n",
    "As seen in this tutorial, MLDB allows you to virtually manipluate data in multiple ways. This grants greater flexibility when constructing models and analyzing complex data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to next?\n",
    "\n",
    "Check out the other [Tutorials and Demos](../../../../doc/#builtin/Demos.md.html)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
